﻿Imports System.Data.OleDb

Module ModuleAtencion



    Public Class C_todos
        '-----------------------------------------------------
        'Funcion para obtener Todos los datos para los combos
        '-----------------------------------------------------
        Public Function allAuditados() As OleDbDataReader
            Using cadenaConexBD()
                Dim drAuditados As OleDbDataReader
                Dim instruccion As New OleDbCommand("select id_ado,noap_ado,id_empr from auditado", cadenaConexBD)
                drAuditados = instruccion.ExecuteReader()
                Return drAuditados
                drAuditados.Close()
            End Using
        End Function
        Public Function allControlers() As OleDbDataReader
            Using cadenaConexBD()
                Dim drAuditor As OleDbDataReader
                Dim instruccion As New OleDbCommand("select id_au,noap_au from auditor", cadenaConexBD)
                drAuditor = instruccion.ExecuteReader()
                Return drAuditor
                drAuditor.Close()
            End Using
        End Function
        Public Function allTllamadas() As OleDbDataReader
            Using cadenaConexBD()
                Dim drTllamadas As OleDbDataReader
                Dim instruccion As New OleDbCommand("select id_tllam,desc_tllam from tipo_llamada", cadenaConexBD)
                drTllamadas = instruccion.ExecuteReader()
                Return drTllamadas
                drTllamadas.Close()
            End Using
        End Function

        Public Function AsesoresXempresa() As OleDbDataReader
            Using cadenaConexBD()
                Dim drAseXempr As OleDbDataReader
                Dim instruccion As New OleDbCommand("SELECT auditado.noap_ado FROM auditado INNER JOIN empresa ON auditado.id_empr = empresa.id_empr WHERE empresa.nomb_empr = '" & frmEdicionAtencion.cbo_empresa.Text & "'", cadenaConexBD)
                drAseXempr = instruccion.ExecuteReader()
                Return drAseXempr
                drAseXempr.Close()
            End Using
        End Function
        Public Function AsesoresXempresa2() As OleDbDataReader
            Using cadenaConexBD()
                Dim drAseXempr As OleDbDataReader
                Dim instruccion As New OleDbCommand("SELECT auditado.noap_ado FROM auditado INNER JOIN empresa ON auditado.id_empr = empresa.id_empr WHERE empresa.nomb_empr = '" & frmInfoAsesorAtencion.cbo_empresa.Text & "'", cadenaConexBD)
                drAseXempr = instruccion.ExecuteReader()
                Return drAseXempr
                drAseXempr.Close()
            End Using
        End Function
        Public Function allIDunicoLLam() As OleDbDataReader
            Using cadenaConexBD()
                Dim dridUnico As OleDbDataReader
                Dim instruccion As New OleDbCommand("select idunico_llam from llamada", cadenaConexBD)
                dridUnico = instruccion.ExecuteReader()
                Return dridUnico
                dridUnico.Close()
            End Using
        End Function
        '------------------------------------------------------------------------
        '---------------seccion de valores y opciones de los combos--------------
        '------------------------------------------------------------------------

        Public Function optSaludo() As OleDbDataReader
            Using cadenaConexBD()
                Dim droptS As OleDbDataReader
                Dim instruccion As New OleDbCommand("select id_salu,opt_salu,vaop_salu from saludo", cadenaConexBD)
                droptS = instruccion.ExecuteReader()
                Return droptS
                droptS.Close()
            End Using
        End Function
        Public Function valorSalu(ByVal formCombo As ComboBox) As String
            Try
                cadenaConexBD()
                Dim instruccion As New OleDbCommand("select vaop_salu from saludo where opt_salu = '" & formCombo.SelectedItem & "'", cadenaConexBD)
                Dim obj As Object = instruccion.ExecuteScalar()
                valorSalu = CType(obj, String)
                If valorSalu Is Nothing Then
                    valorSalu = "Inexistente"
                End If
            Catch ex As Exception
                valorSalu = "Error"
            Finally
                cadenaConexBD.Close()
            End Try
        End Function
        Public Function optEmpatia() As OleDbDataReader
            Using cadenaConexBD()
                Dim droptEmpa As OleDbDataReader
                Dim instruccion As New OleDbCommand("select id_emp,opt_emp,vaop_emp from empatia", cadenaConexBD)
                droptEmpa = instruccion.ExecuteReader()
                Return droptEmpa
                droptEmpa.Close()
            End Using
        End Function
        Public Function valorEmpatia(ByVal formCombo As ComboBox) As String
            Try
                cadenaConexBD()
                Dim instruccion As New OleDbCommand("select vaop_emp from empatia where opt_emp = '" & formCombo.SelectedItem & "'", cadenaConexBD)
                Dim obj As Object = instruccion.ExecuteScalar()
                valorEmpatia = CType(obj, String)
                If valorEmpatia Is Nothing Then
                    valorEmpatia = "Inexistente"
                End If
            Catch ex As Exception
                valorEmpatia = "Error"
            Finally
                cadenaConexBD.Close()
            End Try
        End Function
        Public Function optIdagacion() As OleDbDataReader
            Using cadenaConexBD()
                Dim droptind As OleDbDataReader
                Dim instruccion As New OleDbCommand("select id_ind,opt_ind,vaop_ind from indagacion", cadenaConexBD)
                droptind = instruccion.ExecuteReader()
                Return droptind
                droptind.Close()
            End Using
        End Function
        Public Function valorIndagacion(ByVal formCombo As ComboBox) As String
            Try
                cadenaConexBD()
                Dim instruccion As New OleDbCommand("select vaop_ind from indagacion where opt_ind = '" & formCombo.SelectedItem & "'", cadenaConexBD)
                Dim obj As Object = instruccion.ExecuteScalar()
                valorIndagacion = CType(obj, String)
                If valorIndagacion Is Nothing Then
                    valorIndagacion = "Inexistente"
                End If
            Catch ex As Exception
                valorIndagacion = "Error"
            Finally
                cadenaConexBD.Close()
            End Try
        End Function

        Public Function optDispo() As OleDbDataReader

            Using cadenaConexBD()
                Dim droptdisp As OleDbDataReader
                Dim instruccion As New OleDbCommand("select id_disp,opt_disp,vaop_disp from disposicion", cadenaConexBD)
                droptdisp = instruccion.ExecuteReader()
                Return droptdisp
                droptdisp.Close()
            End Using
        End Function
        Public Function valordisposicion(ByVal formCombo As ComboBox) As String
            Try
                cadenaConexBD()
                Dim instruccion As New OleDbCommand("select vaop_disp from disposicion where opt_disp = '" & formCombo.SelectedItem & "'", cadenaConexBD)
                Dim obj As Object = instruccion.ExecuteScalar()
                valordisposicion = CType(obj, String)
                If valordisposicion Is Nothing Then
                    valordisposicion = "Inexistente"
                End If
            Catch ex As Exception
                valordisposicion = "Error"
            Finally
                cadenaConexBD.Close()
            End Try
        End Function
        Public Function optCompro() As OleDbDataReader
            Using cadenaConexBD()
                Dim droptComp As OleDbDataReader
                Dim instruccion As New OleDbCommand("select id_comp,opt_comp,vaop_comp from compromiso", cadenaConexBD)
                droptComp = instruccion.ExecuteReader()
                Return droptComp
                droptComp.Close()
            End Using
        End Function
        Public Function valorCompromiso(ByVal formCombo As ComboBox) As String
            Try
                cadenaConexBD()
                Dim instruccion As New OleDbCommand("select vaop_comp from compromiso where opt_comp = '" & formCombo.SelectedItem & "'", cadenaConexBD)
                Dim obj As Object = instruccion.ExecuteScalar()
                valorCompromiso = CType(obj, String)
                If valorCompromiso Is Nothing Then
                    valorCompromiso = "Inexistente"
                End If
            Catch ex As Exception
                valorCompromiso = "Error"
            Finally
                cadenaConexBD.Close()
            End Try
        End Function
        Public Function optinforma() As OleDbDataReader
            Using cadenaConexBD()
                Dim droptinfo As OleDbDataReader
                Dim instruccion As New OleDbCommand("select id_info,opt_info,vaop_info from informacion", cadenaConexBD)
                droptinfo = instruccion.ExecuteReader()
                Return droptinfo
                droptinfo.Close()
            End Using
        End Function
        Public Function valorinformacion(ByVal formCombo As ComboBox) As String
            Try
                cadenaConexBD()
                Dim instruccion As New OleDbCommand("select vaop_info from informacion where opt_info = '" & formCombo.SelectedItem & "'", cadenaConexBD)
                Dim obj As Object = instruccion.ExecuteScalar()
                valorinformacion = CType(obj, String)
                If valorinformacion Is Nothing Then
                    valorinformacion = "Inexistente"
                End If
            Catch ex As Exception
                valorinformacion = "Error"
            Finally
                cadenaConexBD.Close()
            End Try
        End Function
        Public Function optComunic() As OleDbDataReader
            Using cadenaConexBD()
                Dim droptComuni As OleDbDataReader
                Dim instruccion As New OleDbCommand("select id_com,opt_com,vaop_com from comunicacion", cadenaConexBD)
                droptComuni = instruccion.ExecuteReader()
                Return droptComuni
                droptComuni.Close()
            End Using
        End Function
        Public Function valorComunicacion(ByVal formCombo As ComboBox) As String
            Try
                cadenaConexBD()
                Dim instruccion As New OleDbCommand("select vaop_com from comunicacion where opt_com = '" & formCombo.SelectedItem & "'", cadenaConexBD)
                Dim obj As Object = instruccion.ExecuteScalar()
                valorComunicacion = CType(obj, String)
                If valorComunicacion Is Nothing Then
                    valorComunicacion = "Inexistente"
                End If
            Catch ex As Exception
                valorComunicacion = "Error"
            Finally
                cadenaConexBD.Close()
            End Try
        End Function
        Public Function optCredibilidad() As OleDbDataReader

            Using cadenaConexBD()
                Dim droptCredi As OleDbDataReader
                Dim instruccion As New OleDbCommand("select id_cred,opt_cred,vaop_cred from credibilidad", cadenaConexBD)
                droptCredi = instruccion.ExecuteReader()
                Return droptCredi
                droptCredi.Close()
            End Using
        End Function
        Public Function valorCredibilidad(ByVal formCombo As ComboBox) As String
            Try
                cadenaConexBD()
                Dim instruccion As New OleDbCommand("select vaop_cred from credibilidad where opt_cred = '" & formCombo.SelectedItem & "'", cadenaConexBD)
                Dim obj As Object = instruccion.ExecuteScalar()
                valorCredibilidad = CType(obj, String)
                If valorCredibilidad Is Nothing Then
                    valorCredibilidad = "Inexistente"
                End If
            Catch ex As Exception
                valorCredibilidad = "Error"
            Finally
                cadenaConexBD.Close()
            End Try
        End Function
        Public Function optCierre() As OleDbDataReader

            Using cadenaConexBD()
                Dim droptCi As OleDbDataReader
                Dim instruccion As New OleDbCommand("select id_cie,opt_cie,vaop_cie from cierre", cadenaConexBD)
                droptCi = instruccion.ExecuteReader()
                Return droptCi
                droptCi.Close()
            End Using
        End Function
        Public Function valorClose(ByVal formCombo As ComboBox) As String
            Try
                cadenaConexBD()
                Dim instruccion As New OleDbCommand("select vaop_cie from cierre where opt_cie = '" & formCombo.SelectedItem & "'", cadenaConexBD)
                Dim obj As Object = instruccion.ExecuteScalar()
                valorClose = CType(obj, String)
                If valorClose Is Nothing Then
                    valorClose = "Inexistente"
                End If
            Catch ex As Exception
                valorClose = "Error"
            Finally
                cadenaConexBD.Close()
            End Try
        End Function
    End Class
    '==================================================================
    'Clase y funciones para agregar datos nuevos a la BD
    '==================================================================
    Public Class C_agregarDatos
        Public Sub NuevoAuditor(ByVal Variable As String)
            Try
                cadenaConexBD()
                Using cadenaConexBD()
                    Dim instruccion As New OleDbCommand("insert into auditor (noap_au) values('" & Variable & "')", cadenaConexBD)
                    instruccion.ExecuteNonQuery()
                End Using
                MessageBox.Show("Nuevos datos agregados con éxito")
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
        End Sub
        
        Public Sub nuevoAuditado(ByVal variable As String)
            Try
                cadenaConexBD()
                Using cadenaConexBD()
                    Dim buscarEmpresa As New OleDbCommand("Select id_empr from empresa where nomb_empr = '" & frmNewAuditado.cbo_empr.SelectedItem & "'", cadenaConexBD)
                    Dim idEmpresa = buscarEmpresa.ExecuteScalar()
                    Dim instruccion As New OleDbCommand("Insert into auditado (noap_ado, id_empr) values ('" & variable & "'," & idEmpresa & ")", cadenaConexBD)
                    instruccion.ExecuteNonQuery()
                End Using
                MsgBox("Cargado Nuevo Auditado", MsgBoxStyle.Information, "Auditoria")
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
        End Sub
        Public Sub guardarLLamada()
            Try
                cadenaConexBD()
                Using cadenaConexBD()
                    'id tipo de llamada 
                    Dim buscaridTllamada As New OleDbCommand("Select id_tllam from tipo_llamada where desc_tllam = '" & frmEdicionAtencion.cbo_tllamada.SelectedItem & "'", cadenaConexBD)
                    Dim idtllamada = buscaridTllamada.ExecuteScalar()
                    'id de la empresa
                    Dim buscaridEmpresa As New OleDbCommand("Select id_empr from empresa where nomb_empr = '" & frmEdicionAtencion.cbo_empresa.SelectedItem & "'", cadenaConexBD)
                    Dim idEmpresa = buscaridEmpresa.ExecuteScalar()
                    'id auditor 
                    Dim buscaridAuditor As New OleDbCommand("Select id_au from auditor where noap_au = '" & frmEdicionAtencion.cbo_controla.SelectedItem & "'", cadenaConexBD)
                    Dim idAuditor = buscaridAuditor.ExecuteScalar()
                    'id auditado
                    Dim buscaridAuditado As New OleDbCommand("Select id_ado from auditado where noap_ado = '" & frmEdicionAtencion.cbo_auditado.SelectedItem & "'", cadenaConexBD)
                    Dim idAuditado = buscaridAuditado.ExecuteScalar()
                    'inserccion de datos a la tabla
                    Dim instruccion As New OleDbCommand("Insert into llamada (fau_llam, fec_llam, nlot_llam, id_tllam,id_empr, id_au, id_ado, vaop_salu, vaop_emp, vaop_ind, vaop_disp, vaop_comp, vaop_info, vaop_com, vaop_cred, vaop_cie, tfav_llam, tctra_llam, idunico_llam, vato_llam,caso_llam) values ('" & frmEdicionAtencion.dtp_hoy.Value & "','" & frmEdicionAtencion.dtp_fllam.Value & "','" & (frmEdicionAtencion.cboLoteLetra.Text + frmEdicionAtencion.cboLoteNum.Text) & "'," & idtllamada & "," & idEmpresa & "," & idAuditor & "," & idAuditado & ",'" & frmEdicionAtencion.txtv01.Text & "','" & frmEdicionAtencion.txtv02.Text & "','" & frmEdicionAtencion.txtv03.Text & "','" & frmEdicionAtencion.txtv04.Text & "','" & frmEdicionAtencion.txtv05.Text & "','" & frmEdicionAtencion.txtv06.Text & "','" & frmEdicionAtencion.txtv07.Text & "','" & frmEdicionAtencion.txtv08.Text & "','" & frmEdicionAtencion.txtv09.Text & "','" & frmEdicionAtencion.rtxt_afavor.Text & "','" & frmEdicionAtencion.rtxt_encontra.Text & "','" & frmEdicionAtencion.lblIdunicoLlam.Text & "','" & frmEdicionAtencion.lbl_puntaje.Text & "','" & frmEdicionAtencion.txtCaso.Text & "' )", cadenaConexBD)
                    instruccion.ExecuteNonQuery()
                End Using
                MsgBox("Auditoria guardada con Éxito", MsgBoxStyle.Information, "Auditoria")
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
        End Sub

    End Class
    '-------------------------------------------------------------
    'Funciones para la carga de datos en los respectrivos combos 
    '-------------------------------------------------------------
    Public Sub CargarAuditados(ByVal lista As ComboBox)
        lista.Items.Clear()
        Dim audi As New C_todos
        Dim drAudi As OleDbDataReader = audi.allAuditados

        If drAudi Is Nothing Then
            Exit Sub
        End If
        Dim strloc As String
        While drAudi.Read()
            strloc = ""
            If Not drAudi.IsDBNull(0) Then
                strloc = drAudi.GetString(1)
            End If
            lista.Items.Add(strloc)
        End While
        drAudi.Close()
    End Sub
    Public Sub CargarAudiXempresa(ByVal lista As ComboBox)
        lista.Items.Clear()
        Dim audi As New C_todos
        Dim drAudi As OleDbDataReader = audi.AsesoresXempresa

        If drAudi Is Nothing Then
            Exit Sub
        End If
        Dim strloc As String
        While drAudi.Read()
            strloc = ""
            If Not drAudi.IsDBNull(0) Then
                strloc = drAudi.GetString(0)
            End If
            lista.Items.Add(strloc)
        End While
        drAudi.Close()
    End Sub
    Public Sub CargarAudiXempresa2(ByVal lista As ComboBox)
        lista.Items.Clear()
        Dim audi As New C_todos
        Dim drAudi As OleDbDataReader = audi.AsesoresXempresa2

        If drAudi Is Nothing Then
            Exit Sub
        End If
        Dim strloc As String
        While drAudi.Read()
            strloc = ""
            If Not drAudi.IsDBNull(0) Then
                strloc = drAudi.GetString(0)
            End If
            lista.Items.Add(strloc)
        End While
        drAudi.Close()
    End Sub
    Public Sub CargarAuditor(ByVal lista As ComboBox)
        lista.Items.Clear()
        Dim auditor As New C_todos
        Dim drAuditor As OleDbDataReader = auditor.allControlers

        If drAuditor Is Nothing Then
            Exit Sub
        End If
        Dim strloc As String
        While drAuditor.Read()
            strloc = ""
            If Not drAuditor.IsDBNull(0) Then
                strloc = drAuditor.GetString(1)
            End If
            lista.Items.Add(strloc)
        End While
        drAuditor.Close()
    End Sub

    Public Sub CargarTllamada(ByVal lista As ComboBox)
        lista.Items.Clear()
        Dim tllama As New C_todos
        Dim drtllam As OleDbDataReader = tllama.allTllamadas

        If drtllam Is Nothing Then
            Exit Sub
        End If
        Dim strloc As String
        While drtllam.Read()
            strloc = ""
            If Not drtllam.IsDBNull(0) Then
                strloc = drtllam.GetString(1)
            End If
            lista.Items.Add(strloc)
        End While
        drtllam.Close()
    End Sub
    Public Sub CargarOptSalu(ByVal lista As ComboBox)
        lista.Items.Clear()
        Dim optS As New C_todos
        Dim droptS As OleDbDataReader = optS.optSaludo

        If droptS Is Nothing Then
            Exit Sub
        End If
        Dim strloc As String
        While droptS.Read()
            strloc = ""
            If Not droptS.IsDBNull(0) Then
                strloc = droptS.GetString(1)
            End If
            lista.Items.Add(strloc)
        End While
        droptS.Close()
    End Sub
    Public Sub CargarOptEmpa(ByVal lista As ComboBox)
        lista.Items.Clear()
        Dim optE As New C_todos
        Dim droptE As OleDbDataReader = optE.optEmpatia

        If droptE Is Nothing Then
            Exit Sub
        End If
        Dim strloc As String
        While droptE.Read()
            strloc = ""
            If Not droptE.IsDBNull(0) Then
                strloc = droptE.GetString(1)
            End If
            lista.Items.Add(strloc)
        End While
        droptE.Close()
    End Sub
    Public Sub CargarOptInd(ByVal lista As ComboBox)
        lista.Items.Clear()
        Dim optind As New C_todos
        Dim droptind As OleDbDataReader = optind.optIdagacion
        If droptind Is Nothing Then
            Exit Sub
        End If
        Dim strloc As String
        While droptind.Read()
            strloc = ""
            If Not droptind.IsDBNull(0) Then
                strloc = droptind.GetString(1)
            End If
            lista.Items.Add(strloc)
        End While
        droptind.Close()
    End Sub
    Public Sub CargarOptDisp(ByVal lista As ComboBox)
        lista.Items.Clear()
        Dim optDisp As New C_todos
        Dim droptDisp As OleDbDataReader = optDisp.optDispo
        If droptDisp Is Nothing Then
            Exit Sub
        End If
        Dim strloc As String
        While droptDisp.Read()
            strloc = ""
            If Not droptDisp.IsDBNull(0) Then
                strloc = droptDisp.GetString(1)
            End If
            lista.Items.Add(strloc)
        End While
        droptDisp.Close()
    End Sub
    Public Sub CargarOptComp(ByVal lista As ComboBox)
        lista.Items.Clear()
        Dim optComp As New C_todos
        Dim droptComp As OleDbDataReader = optComp.optCompro
        If droptComp Is Nothing Then
            Exit Sub
        End If
        Dim strloc As String
        While droptComp.Read()
            strloc = ""
            If Not droptComp.IsDBNull(0) Then
                strloc = droptComp.GetString(1)
            End If
            lista.Items.Add(strloc)
        End While
        droptComp.Close()
    End Sub
    Public Sub CargarOptInfo(ByVal lista As ComboBox)
        lista.Items.Clear()
        Dim optinfo As New C_todos
        Dim droptInfo As OleDbDataReader = optinfo.optinforma
        If droptInfo Is Nothing Then
            Exit Sub
        End If
        Dim strloc As String
        While droptInfo.Read()
            strloc = ""
            If Not droptInfo.IsDBNull(0) Then
                strloc = droptInfo.GetString(1)
            End If
            lista.Items.Add(strloc)
        End While
        droptInfo.Close()
    End Sub
    Public Sub CargarOptComunicacion(ByVal lista As ComboBox)
        lista.Items.Clear()
        Dim optComun As New C_todos
        Dim droptComun As OleDbDataReader = optComun.optComunic
        If droptComun Is Nothing Then
            Exit Sub
        End If
        Dim strloc As String
        While droptComun.Read()
            strloc = ""
            If Not droptComun.IsDBNull(0) Then
                strloc = droptComun.GetString(1)
            End If
            lista.Items.Add(strloc)
        End While
        droptComun.Close()
    End Sub
    Public Sub CargarOptCredibilidad(ByVal lista As ComboBox)
        lista.Items.Clear()
        Dim optCredd As New C_todos
        Dim droptCredd As OleDbDataReader = optCredd.optCredibilidad
        If droptCredd Is Nothing Then
            Exit Sub
        End If
        Dim strloc As String
        While droptCredd.Read()
            strloc = ""
            If Not droptCredd.IsDBNull(0) Then
                strloc = droptCredd.GetString(1)
            End If
            lista.Items.Add(strloc)
        End While
        droptCredd.Close()
    End Sub
    Public Sub CargarOptClose(ByVal lista As ComboBox)
        lista.Items.Clear()
        Dim optCl As New C_todos
        Dim droptClo As OleDbDataReader = optCl.optCierre
        If droptClo Is Nothing Then
            Exit Sub
        End If
        Dim strloc As String
        While droptClo.Read()
            strloc = ""
            If Not droptClo.IsDBNull(0) Then
                strloc = droptClo.GetString(1)
            End If
            lista.Items.Add(strloc)
        End While
        droptClo.Close()
    End Sub
    
End Module
